-- Start of DDL Script for Package PEMEX2.PKG_CATALOGOS
-- Generated 17/04/2012 22:40:34 from PEMEX2@XE

CREATE OR REPLACE 
PACKAGE pkg_catalogos
  IS
 
   FUNCTION getTipoClasificacionServicio
   RETURN sys_refcursor;

  --FUNCTION getTipoInstalacion RETURN sys_refcursor;
  --FUNCTION getTiposServicio RETURN sys_refcursor;

  procedure getTiposServicios(resultado out sys_refcursor);
  procedure getActivos(resultado out sys_refcursor);
  procedure getCampos (resultado out sys_refcursor);
  procedure getEstatus(resultado out sys_refcursor);
  PROCEDURE getAreas(resultado out sys_refcursor);
  FUNCTION getInstalacion(pcve_campo number, mascara varchar2) RETURN sys_refcursor;
  --FUNCTION getProyectos  RETURN sys_refcursor;
  PROCEDURE getProyectos (resultado out sys_refcursor);
  procedure getMunicipios(resultado out sys_refcursor);
  procedure getLocalidades(cve_municpio varchar2,resultado out sys_refcursor);
  procedure getAcreditacion(resultado out sys_refcursor);
  procedure getPersonas(pcve_tol varchar2, resultado out sys_refcursor);
  procedure getSubordinados(pcve_usuario_padre varchar2, resultado out sys_refcursor);
  --FUNCTION getAvanceOperativo RETURN sys_refcursor;
  procedure getAvanceOperativo (resultado out sys_refcursor);
  FUNCTION getTiposObraComplementaria RETURN sys_refcursor;
  PROCEDURE getUsuario(pcve_usuario in varchar2,pcve_contrasenia in varchar2, resultado out sys_refcursor);
  FUNCTION getJefesInvoSol(pcve_solicitud in varchar2) RETURN sys_refcursor;
  procedure getEstatusObra (resultado out sys_refcursor);
END; -- Package spec
/


CREATE OR REPLACE 
PACKAGE BODY pkg_catalogos
IS

    FUNCTION getTipoClasificacionServicio RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN
    OPEN resultado FOR
        SELECT cve_tipo_simplificacion, descripcion
          FROM cat_tipo_clasificacion_servici a
          order by 2;

    return resultado;
    END;

    /*FUNCTION getTipoInstalacion RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_tipo_instalacion, a.descripcion
          FROM cat_tipo_instalacion a
        order by 2;

    return resultado;
    END;*/

    /* FUNCTION getTiposServicio RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_tipo_servicio, a.descripcion
          FROM cat_tipo_servicio a
        order by 2;

    return resultado;
    END;*/

    procedure getTiposServicios(resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_tipo_servicio, a.descripcion, a.trabajos_realizar
          FROM cat_tipo_servicio a
        order by 2;
    END;



    procedure getActivos(resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT ca.cve_activo, ca.descripcion
          FROM cat_activo ca
        order by 2;
    END;

    procedure getCampos (resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_campo, a.descripcion, a.numero, a.numero_pera,
               a.cve_sector
          FROM cat_campo a
        order by 2;
    END;

  procedure getEstatus(resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT ce.cve_estatus, ce.descripcion
          FROM cat_estatus ce
        order by 2;
    END;


    FUNCTION getInstalacion(pcve_campo number, mascara varchar2) RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_instalacion, a.descripcion, a.cve_campo
        FROM cat_instalacion a where cve_campo = pcve_campo and homotext(descripcion) like '%'||homotext(mascara)||'%'
        order by 2;

    return resultado;
    END;
    PROCEDURE getAreas(resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT cs.cve_solicitante, cs.descripcion
          FROM cat_solicitante cs
        order by 2;
    END;

    /*FUNCTION getProyectos  RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_tipo_proyecto, a.descripcion
        FROM cat_tipo_proyecto a
        order by 2;

    return resultado;
    END
    */

    procedure  getProyectos (resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_tipo_proyecto, a.descripcion
        FROM cat_tipo_proyecto a
        order by 2;
    END;



procedure getMunicipios(resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
            select cm.cve_municipio
                  ,cm.descripcion
            from  cat_municipio cm
               order by descripcion;

    END;
procedure getLocalidades(cve_municpio varchar2,resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR

            select cloc.cve_localidad as clave, cloc.cve_municipio
                  ,cloc.descripcion
            from cat_localidades cloc
            where cloc.cve_municipio=cve_municpio
            order by cloc.descripcion;
    END;
procedure getAcreditacion(resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR

            select cta.cve_tipo_acreditacion as clave
                  ,cta.descripcion
             from cat_tipo_acreditacion cta;
    END;

    procedure getPersonas(pcve_tol varchar2,resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_usuario, a.cve_rol, a.nombre||' '||a.apellido_p||' '||a.apellido_m nombre,
               a.ficha, a.no_cedula
        FROM cat_usuario a
        WHERE
               a.cve_rol = pcve_tol
        order by 2;
    END;

    /* Obtiene todos los empleados que estan acargo de un empleado. y que sean de algun tipo en especifico*/
    procedure getSubordinados(pcve_usuario_padre varchar2,resultado out sys_refcursor)
    IS
    ROL_BUSCAR NUMBER(2);
    BEGIN
    -- BUSCA EL ROL DE USUARIO Y LO INCREMENTA EN UNO PARA TRAER A SSUS SOBORDINADOS
    SELECT CVE_ROL INTO ROL_BUSCAR FROM CAT_USUARIO WHERE CVE_USUARIO = pcve_usuario_padre;

    IF (ROL_BUSCAR = 2 or ROL_BUSCAR = 3)THEN -- UN SUPERVISOR DEBE VER A TODOS LOS GESTORES
        OPEN resultado FOR
                SELECT a.cve_usuario, a.cve_rol,B.descripcion||'-'|| a.nombre||' '||a.apellido_p||' '||a.apellido_m nombre,
                       a.ficha, a.no_cedula, A.cve_usuario_padre,a.apellido_p,a.apellido_m,A.contrasenia,B.descripcion DESC_ROL
                FROM cat_usuario a, CAT_ROL B
                WHERE
                A.cve_rol = B.CVE_ROL
                AND   (    cve_usuario in ( SELECT CVE_USUARIO FROM CAT_USUARIO START WITH CVE_USUARIO = pcve_usuario_padre
                                        CONNECT BY PRIOR CVE_USUARIO = CVE_USUARIO_PADRE
                       )
                       OR a.cve_rol = ROL_BUSCAR +1 )
                order by 2;

    ELSIF (ROL_BUSCAR = 10) THEN    -- ES UN COORDINADOR. DEBE VER A TODOS
        OPEN resultado FOR
            SELECT a.cve_usuario, a.cve_rol,B.descripcion||'-'|| a.nombre||' '||a.apellido_p||' '||a.apellido_m nombre,
                   a.ficha, a.no_cedula, A.cve_usuario_padre,a.apellido_p,a.apellido_m,A.contrasenia,B.descripcion DESC_ROL
            FROM cat_usuario a, CAT_ROL B

            WHERE
                     A.cve_rol = B.CVE_ROL
            ORDER BY  a.cve_rol,a.nombre||' '||a.apellido_p||' '||a.apellido_m;
    ELSE
        OPEN resultado FOR
            SELECT a.cve_usuario, a.cve_rol,B.descripcion||'-'|| a.nombre||' '||a.apellido_p||' '||a.apellido_m nombre,
                   a.ficha, a.no_cedula, A.cve_usuario_padre,a.apellido_p,a.apellido_m,A.contrasenia,B.descripcion DESC_ROL
            FROM cat_usuario a, CAT_ROL B
            WHERE
                     A.cve_rol = B.CVE_ROL
            AND       cve_usuario in ( SELECT CVE_USUARIO FROM CAT_USUARIO START WITH CVE_USUARIO = pcve_usuario_padre
                                    CONNECT BY PRIOR CVE_USUARIO = CVE_USUARIO_PADRE
                   )
                   and a.cve_rol = ROL_BUSCAR +1
            order by 2;

    END IF;
    END;


 /*FUNCTION getAvanceOperativo RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_avance_operativo, a.descripcion
        FROM cat_avance_operativo a
        order by 1;

    return resultado;
    END;
    */

    procedure getAvanceOperativo (resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_avance_operativo, a.descripcion
        FROM cat_avance_operativo a
        order by 1;
    END;


 FUNCTION getTiposObraComplementaria RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_tipo_obra_complementaria, a.descripcion
         FROM cat_tipo_obra_complementaria a
        order by 2;

    return resultado;
    END;

 PROCEDURE getUsuario(pcve_usuario in varchar2,pcve_contrasenia in varchar2, resultado out sys_refcursor)
    IS
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_usuario, a.cve_rol, a.nombre, a.apellido_p, a.apellido_m,
               a.ficha, a.no_cedula, a.nombre_usuario, a.nombre_pc_mtto,
               a.num_ip_pc_mtto, a.fecha_ultimo_cambio, a.fecha_registro,
               a.cve_estatus_registro, a.cve_usuario_mtto, a.cve_usuario_padre,
               a.num_orden, a.contrasenia, b.descripcion desc_rol, a.cve_area
        FROM cat_usuario a, cat_rol b
        WHERE upper(trim(a.nombre_usuario)) = upper(pcve_usuario)
        and   upper(trim(a.contrasenia)) = upper(pcve_contrasenia)
        and a.cve_rol = b.cve_rol;

    END;

 procedure getEstatusObra (resultado out sys_refcursor)
    IS

    BEGIN
    OPEN resultado FOR
        SELECT a.cve_estatus, a.descripcion
          FROM cat_estatus_obra a
        order by 2;

    END;

    /*Recupera todos los jefes de sector involucrados en la solicitud. util en la captura de la obra por parte de un control de obra*/
 FUNCTION getJefesInvoSol(pcve_solicitud in varchar2) RETURN sys_refcursor
    IS
    resultado sys_refcursor;
    BEGIN
    OPEN resultado FOR
        SELECT a.cve_usuario, a.cve_rol,firstcap(TRIM(a.nombre)||' '||TRIM(a.apellido_p)||' '||a.apellido_m) nombre,
               a.ficha, a.no_cedula, A.cve_usuario_padre
        FROM cat_usuario a,CAT_ROL B
        where
             A.cve_rol = B.CVE_ROL and
             cve_usuario in ( SELECT CVE_USUARIO FROM TURNAR_JEFE_AREA WHERE CVE_SOLICITUD = pcve_solicitud)
             ORDER BY NOMBRE;

    return resultado;
    END;
END;
/


-- End of DDL Script for Package PEMEX2.PKG_CATALOGOS
